﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using DBFile.Core.Interfaces;
using DBFile.Core.Web;
using DBFile.SqlServerProvider;

namespace DBFile.Providers.Impl
{
	internal class StoredProcedure : IDBFileProvider
	{
		#region Constants

		// constants for the database columns
		private const int FileName = 0;
		private const int ContentLength = 1;
		private const int MimeType = 2;
		private const int FileStream = 3;

		// constants for the SQL to use 
		private const string InsertSql = "ds_insert_file";
		private const string UpdateSql = "ds_update_file";
		private const string SingleSelectSql = "ds_get_file";
		private const string SelectSql = "ds_get_file_list";
		private const string DeleteSql = "ds_delete_file";
		private const string SelectCountSql = "ds_get_count";
		private const string CopySql = "ds_copy_file";

		#endregion

		private string connectionString;

		internal StoredProcedure(string connectionString)
		{
			this.connectionString = connectionString;
		}

		public int Copy(string sourceFilename, string destinationFilename, bool overwrite)
		{
			// check to see if it exists
			bool existing = Exists(destinationFilename);
			int rowsAffected = 0;
			SqlConnection conn = null;
			if (existing == false || overwrite == true)
			{
				using (conn = new SqlConnection(connectionString))
				{
					SqlCommand cmd = new SqlCommand(CopySql, conn);
					cmd.CommandType = CommandType.StoredProcedure;

					SqlParameter p = new SqlParameter("@DestFileName", SqlDbType.VarChar, 255);
					p.Value = destinationFilename;
					cmd.Parameters.Add(p);

					p = new SqlParameter("@SourceFileName", SqlDbType.VarChar, 255);
					p.Value = sourceFilename;
					cmd.Parameters.Add(p);

					conn.Open();
					rowsAffected = cmd.ExecuteNonQuery();

					conn.Close();
				}
			}
			return rowsAffected;
		}

		public bool Exists(string filename)
		{
			SqlConnection conn = null;
			int existingRows = 0;
			using (conn = new SqlConnection(connectionString))
			{
				SqlCommand cmd = new SqlCommand(SelectCountSql, conn);
				cmd.CommandType = CommandType.StoredProcedure;

				SqlParameter p = new SqlParameter("@FileName", SqlDbType.VarChar, 255);
				p.Value = filename;
				cmd.Parameters.Add(p);

				conn.Open();
				existingRows = (int)cmd.ExecuteScalar();

				conn.Close();
			}
			return (existingRows > 0);
		}

		public int Delete(string filename)
		{
			if (filename.Length == 0)
				return 0;

			SqlConnection conn = null;
			int rowsAffected = 0;

			using (conn = new SqlConnection(connectionString))
			{
				SqlCommand cmd = new SqlCommand(DeleteSql, conn);
				cmd.CommandType = CommandType.StoredProcedure;

				SqlParameter p = new SqlParameter("@FileName", SqlDbType.VarChar, 255);
				p.Value = filename;
				cmd.Parameters.Add(p);

				conn.Open();
				rowsAffected = cmd.ExecuteNonQuery();
				conn.Close();
			}
			return rowsAffected;
		}

		public HttpPostedFile Get(string filename)
		{
            return Get<HttpPostedFile>(filename);
		}

        public T Get<T>(string filename) where T : IDBFile, new()
        {
            T result = new T();

            SqlConnection conn = null;
            using (conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(SingleSelectSql, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter p = new SqlParameter("@FileName", SqlDbType.VarChar, 255);
                p.Value = filename;
                cmd.Parameters.Add(p);

                conn.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                // current implementation is only interested in one file, duplicates are not currently handled
                if (rdr.Read())
                {
                    result.FileName = rdr[FileName].ToString();
                    result.ContentLength = rdr.GetInt32(ContentLength);
                    result.ContentType = rdr[MimeType].ToString();

                    byte[] filebytes = (byte[])rdr[FileStream];
                    result.FileBytes = new byte[filebytes.Length];
                    Array.Copy(filebytes, result.FileBytes, filebytes.Length);
                }
                conn.Close();
            }
            return result;
        }

		public string[] GetFileNameList()
		{
            List<string> result = new List<string>();
            SqlConnection conn = null;
			using (conn = new SqlConnection(connectionString))
			{
				SqlCommand cmd = new SqlCommand(SelectSql, conn);
				cmd.CommandType = CommandType.StoredProcedure;

				conn.Open();
				SqlDataReader dr = cmd.ExecuteReader();
				while (dr.Read())
				{
					result.Add(dr.GetString(FileName));
				}
				conn.Close();
			}

			return result.ToArray();
		}

		public int Insert(string filename, byte[] buffer, string contentType, long contentLength)
		{
			SqlConnection conn = null;
			int rowsAffected = 0;

			using (conn = new SqlConnection(connectionString))
			{
				SqlCommand cmd = new SqlCommand(InsertSql, conn);
				cmd.CommandType = CommandType.StoredProcedure;

				SqlParameter p = new SqlParameter("@FileName", SqlDbType.VarChar, 255);
				p.Value = filename;
				cmd.Parameters.Add(p);

				p = new SqlParameter("@ContentLength", SqlDbType.Int);
				p.Value = contentLength;
				cmd.Parameters.Add(p);

				p = new SqlParameter("@MimeType", SqlDbType.VarChar, 255);
				p.Value = contentType;
				cmd.Parameters.Add(p);

				conn.Open();

				p = new SqlParameter("@FileStream", SqlServerHelper.GetFileColumnType(conn));
				p.Value = buffer;
				cmd.Parameters.Add(p);
								
				rowsAffected = cmd.ExecuteNonQuery();
				conn.Close();
			}
			return rowsAffected;
		}

		public int Insert(string filename, byte[] buffer, string contentType, long contentLength, bool overwrite)
		{
			bool exists = false;
			int rowsAffected = 0;

			// check to see if the file exists
			exists = Exists(filename);

			if ((exists == true && overwrite == false) || exists == false)
			{
				// do insert
				rowsAffected = Insert(filename, buffer, contentType, contentLength);
			}
			else
			{
				// update existing
				rowsAffected = Update(filename, buffer, contentType, contentLength);
			}

			return rowsAffected;
		}

		public int Update(string filename, byte[] buffer, string contentType, long contentLength)
		{
			SqlConnection conn = null;
			int rowsAffected = 0;

			using (conn = new SqlConnection(connectionString))
			{
				SqlCommand cmd = new SqlCommand(UpdateSql, conn);
				cmd.CommandType = CommandType.StoredProcedure;

				SqlParameter p = new SqlParameter("@ContentLength", SqlDbType.Int);
				p.Value = contentLength;
				cmd.Parameters.Add(p);

				p = new SqlParameter("@MimeType", SqlDbType.VarChar, 255);
				p.Value = contentType;
				cmd.Parameters.Add(p);

				p = new SqlParameter("@FileName", SqlDbType.VarChar, 255);
				p.Value = filename;
				cmd.Parameters.Add(p);

				conn.Open();

				p = new SqlParameter("@FileStream", SqlServerHelper.GetFileColumnType(conn));
				p.Value = buffer;
				cmd.Parameters.Add(p);

				rowsAffected = cmd.ExecuteNonQuery();
				conn.Close();
			}
			return rowsAffected;
		}
	}
}
